﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Windows.Forms;
using QuanLyBanHang.DataConnection;

namespace QuanLyBanHang.QuerySQL
{
    class ClassTKSP
    {
        Connect conn = new Connect();
        public DataTable ShowCTHD(int i, string ma = null)
        {
            DataTable tb = new DataTable();
            switch (i)
            {
                case 0:
                    {
                        try
                        {
                            string sql = "select *from CHITIET_HD";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            string sql = "select *from SANPHAM";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 3:
                    {
                        try
                        {
                            string sql = "select *from SANPHAM where MASP = '"+ma+"'";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 2:
                    {
                        try
                        {
                            string sql = "select *from CHITIET_HD where MASP like '%" + ma + "%'";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
            }

            return tb;
        }
        public DataTable TKCT(string tk)
        {
            DataTable tb = new DataTable(); ;
            try
            {
                string sql = "select CHITIET_HD.MAHD,MASP,TENSP,GIABAN,SOLUONG,THANHTIEN from HOADON,CHITIET_HD where " +
                             "HOADON.MAHD = CHITIET_HD.MAHD  and year(NGAYLAP) = 2014 and month(NGAYLAP) = 6 and day(NGAYLAP) = 5 and " +
                             "(MASP = '" + tk + "' or TENSP = N'" + tk + "')";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        public DataTable TKSL(int i,string tk,int y = 0, int m = 0,int d = 0)
        {
            DataTable tb = new DataTable();
            switch (i)
            {
                case 0:
                    {
                        try
                        {
                            string sql = "select sum(SOLUONG) as SL from HOADON,CHITIET_HD where HOADON.MAHD = CHITIET_HD.MAHD  and year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m + " and day(NGAYLAP) = " + d + " and  MASP = '" + tk + "'";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            string sql = "select sum(SOLUONG) as SL from HOADON,CHITIET_HD where HOADON.MAHD = CHITIET_HD.MAHD  and year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m + " and  MASP = '" + tk + "'";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 2:
                    {
                        try
                        {
                            string sql = "select sum(SOLUONG) as SL from HOADON,CHITIET_HD where HOADON.MAHD = CHITIET_HD.MAHD  and year(NGAYLAP) = " + y + " and  MASP = '" + tk + "'";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
            }
            return tb;
        }
    }
}
